[CODE GENERATION] How to generate DELETE statements in PL/SQL, based on the tables FK relations?
Posted
by The chicken in the kitchen
on Stack Overflow
See other posts from Stack Overflow
or by The chicken in the kitchen
Published on 2010-04-20T17:02:25Z
Indexed on
2010/04/20
20:53 UTC
Read the original article
Hit count: 263
Is it possible via script/tool to generate authomatically many delete statements based on the tables fk relations, using Oracle PL/SQL?
In example: I have the table: CHICKEN (CHICKEN_CODE NUMBER) and there are 30 tables with fk references to its CHICKEN_CODE that I need to delete; there are also other 150 tables foreign-key-linked to that 30 tables that I need to delete first.
Is there some tool/script PL/SQL that I can run in order to generate all the necessary delete statements based on the FK relations for me?
(by the way, I know about cascade delete on the relations, but please pay attention: I CAN'T USE IT IN MY PRODUCTION DATABASE, because it's dangerous!)
I'm using Oracle DataBase 10G R2.
This is the result I've written, but it is not recursive:
This is a view I have previously written, but of course it is not recursive!
CREATE OR REPLACE FORCE VIEW RUN
(
OWNER_1,
CONSTRAINT_NAME_1,
TABLE_NAME_1,
TABLE_NAME,
VINCOLO
)
AS
SELECT OWNER_1,
CONSTRAINT_NAME_1,
TABLE_NAME_1,
TABLE_NAME,
'('
|| LTRIM (
EXTRACT (XMLAGG (XMLELEMENT ("x", ',' || COLUMN_NAME)),
'/x/text()'),
',')
|| ')'
VINCOLO
FROM ( SELECT CON1.OWNER OWNER_1,
CON1.TABLE_NAME TABLE_NAME_1,
CON1.CONSTRAINT_NAME CONSTRAINT_NAME_1,
CON1.DELETE_RULE,
CON1.STATUS,
CON.TABLE_NAME,
CON.CONSTRAINT_NAME,
COL.POSITION,
COL.COLUMN_NAME
FROM DBA_CONSTRAINTS CON,
DBA_CONS_COLUMNS COL,
DBA_CONSTRAINTS CON1
WHERE CON.OWNER = 'TABLE_OWNER' AND CON.TABLE_NAME = 'TABLE_OWNED'
AND ( (CON.CONSTRAINT_TYPE = 'P')
OR (CON.CONSTRAINT_TYPE = 'U'))
AND COL.TABLE_NAME = CON1.TABLE_NAME
AND COL.CONSTRAINT_NAME = CON1.CONSTRAINT_NAME
--AND CON1.OWNER = CON.OWNER
AND CON1.R_CONSTRAINT_NAME = CON.CONSTRAINT_NAME
AND CON1.CONSTRAINT_TYPE = 'R'
GROUP BY CON1.OWNER,
CON1.TABLE_NAME,
CON1.CONSTRAINT_NAME,
CON1.DELETE_RULE,
CON1.STATUS,
CON.TABLE_NAME,
CON.CONSTRAINT_NAME,
COL.POSITION,
COL.COLUMN_NAME)
GROUP BY OWNER_1,
CONSTRAINT_NAME_1,
TABLE_NAME_1,
TABLE_NAME;
... and it contains the error of using DBA_CONSTRAINTS instead of ALL_CONSTRAINTS...
© Stack Overflow or respective owner